package com.amazon.service.impl;

import com.amazon.constant.Constant;
import com.amazon.model.*;
import com.amazon.service.BusinessReportService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.Month;
import java.util.*;

@Service
public class BusinessReportServiceImpl implements BusinessReportService {
    @Override
    public MonthModel businessReport(Long startTime, Long endTime, Integer mouth) {
        MonthModel monthModel = new MonthModel();
        InputStream inputStream = null;
        Workbook workbook = null;
        List<Long> dates = new ArrayList<>();
        List<String> dateStr = new ArrayList<>();
        try {
            Calendar calendar = Calendar.getInstance();
            calendar.setTimeInMillis(startTime);
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat monthsdf = new SimpleDateFormat("yyyy-M");
            int  num = 2;
            for (int i = 0; i < mouth; i++) {
                calendar.add(Calendar.MONTH, num); // 将日期增加i+1个月
                calendar.set(Calendar.DAY_OF_MONTH, 1); // 设置为该月的第一天
                String formattedDate = sd.format(calendar.getTime());
                dateStr.add(formattedDate);
                dates.add(calendar.getTime().getTime());
                num =1;
            }
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(1);

            // 跳过第一行
            int rowOffset = 1;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            // 6个月的
            Integer totalOrderItemsAll = 0;
            Integer sessionsTotalAll = 0;
            Map<String, List<DaySaleModel>> dayMap = new HashMap<>();
            for (int rowNum = sheet.getFirstRowNum() + rowOffset; rowNum <= sheet.getLastRowNum();
                 rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                DaySaleModel daySaleModel = new DaySaleModel();
                String month = "";
                // 处理每一行的数据
                for (int colNum = row.getFirstCellNum(); colNum < row.getLastCellNum(); colNum++) {
                    Cell cell = row.getCell(colNum);
                    if (cell == null) {
                        continue;
                    }

                    if(colNum == 0){
                        SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
                        Date date = sdf.parse(cell.toString());
                        long timestamp = date.getTime();
                        month = monthsdf.format(new Date(timestamp));
                        if(timestamp >= startTime && timestamp <= endTime ){
                            daySaleModel.setDate(timestamp);
                            String[] split = month.split("-");
                            String fir = split[0];
                            int some = Integer.parseInt(split[1]) + 1;
                            if (some > 12) {
                                some = 1;
                            }
                            month = fir + "-" + some;
                            daySaleModel.setMouth(month);
                            continue;
                        } else {
                            daySaleModel = null;
                            break;
                        }
                    }
                    CellValue cellValue = evaluator.evaluate(cell);
                    if (cellValue ==null){
                        continue;
                    }
                    // 统计每一个单元格数据
                    double value = cellValue.getNumberValue();
                    if(colNum == 1){
                        daySaleModel.setTotalOrderItems((int)value);
                        totalOrderItemsAll += (int)value;
                    }
                    if(colNum == 2){
                        daySaleModel.setUnitsOrdered((int)value);
                    }
                    if(colNum == 3){
                        daySaleModel.setOrderedProductSales(value);
                    }
                    if(colNum == 4){
                        daySaleModel.setUnitsOrderItem(value);
                    }
                    if(colNum == 5){
                        daySaleModel.setSalesOrderItem(value);
                    }
                    if(colNum == 11){
                        daySaleModel.setSessionsTotal((int)value);
                        sessionsTotalAll += (int)value;
                    }

                }
                if (daySaleModel != null) {
                    List<DaySaleModel> orDefault = dayMap.getOrDefault(month, new ArrayList<>());
                    orDefault.add(daySaleModel);
                    dayMap.put(month, orDefault);
                }
            }

            int aveOrderItems = totalOrderItemsAll/dates.size()+1;
            int aveSession = sessionsTotalAll/dates.size()+1;
            List<DayValue> months = new ArrayList<>();
            List<DaySaleModel> modelList = new ArrayList<>();

            for (Map.Entry<String, List<DaySaleModel>> entry : dayMap.entrySet()) {
                Integer unitsOrderedTotal = 0;
                Integer totalOrderItemsMouth = 0;
                Double OrderedProductSalesTotal = 0.0;
                Integer sessionsTotal = 0;

                DaySaleModel daySaleModel = new DaySaleModel();
                DayValue dayValue = new DayValue();
                String k = entry.getKey();
                List<DaySaleModel> v = entry.getValue();
                for(DaySaleModel model: v){
                    Double orderedProductSales = model.getOrderedProductSales();
                    OrderedProductSalesTotal += orderedProductSales;
                    Integer unitsOrdered = model.getUnitsOrdered();
                    unitsOrderedTotal += unitsOrdered;
                    Integer totalOrderItems = model.getTotalOrderItems();
                    totalOrderItemsMouth += totalOrderItems;
                    Integer sessions = model.getSessionsTotal();
                    sessionsTotal += sessions;
                }
                String[] split = k.split("-");
                daySaleModel.setMouth(split[1] + "/01" + "/" +split[0]);
                daySaleModel.setDate(v.get(0).getDate());
                BigDecimal bd = new BigDecimal(OrderedProductSalesTotal); // 创建BigDecimal对象
                double result6 = bd.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setOrderedProductSales(result6);
                Random random = new Random();
                int min = 35;
                int max = 45;
                int randomNumber = random.nextInt(max - min + 1) + min;
                double a = OrderedProductSalesTotal/randomNumber;
                BigDecimal bd1 = new BigDecimal(a); // 创建BigDecimal对象
                double result5 = bd1.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setOrderedProductSalesB2B(result5);
                daySaleModel.setUnitsOrdered(unitsOrderedTotal);
                int umin = 30;
                int umax = 35;
                int randomNumber1 = random.nextInt(umax - umin + 1) + min;
                daySaleModel.setUnitsOrderedB2B(unitsOrderedTotal/randomNumber1);
                daySaleModel.setTotalOrderItems(totalOrderItemsMouth);

                daySaleModel.setTotalOrderItemsB2B(totalOrderItemsMouth/randomNumber1);

                BigDecimal bd5 = new BigDecimal(OrderedProductSalesTotal/totalOrderItemsMouth); // 创建BigDecimal对象
                double result9 = bd5.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setSalesOrderItem(result9);
                double c = daySaleModel.getOrderedProductSalesB2B()/daySaleModel.getTotalOrderItemsB2B();
                BigDecimal bd6 = new BigDecimal(c); // 创建BigDecimal对象
                double result10 = bd6.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setSalesOrderItemB2B(result10);

                BigDecimal result = new BigDecimal(unitsOrderedTotal).divide(new BigDecimal(totalOrderItemsMouth), 2, RoundingMode.HALF_UP);
                daySaleModel.setUnitsOrderItem(Double.valueOf(result.toString()));
                BigDecimal result1 = new BigDecimal(daySaleModel.getUnitsOrderedB2B()).divide(new BigDecimal(daySaleModel.getTotalOrderItemsB2B()), 2, RoundingMode.HALF_UP);
                daySaleModel.setUnitsOrderItemB2B(Double.valueOf(result1.toString()));

                BigDecimal bd3 = new BigDecimal(OrderedProductSalesTotal/unitsOrderedTotal); // 创建BigDecimal对象
                double result7 = bd3.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setAverageSellingPrice(result7);
                double b = daySaleModel.getOrderedProductSalesB2B()/daySaleModel.getUnitsOrderedB2B();
                BigDecimal bd4 = new BigDecimal(b); // 创建BigDecimal对象
                double result8 = bd4.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                daySaleModel.setAverageSellingPriceB2B(result8);

                daySaleModel.setSessionsTotal(sessionsTotal);
                daySaleModel.setSessionsTotalB2B(sessionsTotal/randomNumber1);

                BigDecimal result3 = new BigDecimal(totalOrderItemsMouth)
                        .divide(new BigDecimal(sessionsTotal), 2, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100));
                daySaleModel.setOrderItemSessionPercentage(Double.valueOf(result3.toString()));
                BigDecimal result4 = new BigDecimal(daySaleModel.getTotalOrderItemsB2B())
                        .divide(new BigDecimal(daySaleModel.getSessionsTotalB2B()), 2, RoundingMode.HALF_UP)
                        .multiply(new BigDecimal(100));
                daySaleModel.setOrderItemSessionPercentageB2B(Double.valueOf(result4.toString()));
                daySaleModel.setAverageOfferCount(unitsOrderedTotal/randomNumber1);
                modelList.add(daySaleModel);

                //月份转英文
                dayValue.setSortNum(Integer.valueOf(split[1]));
                String name = Month.of(Integer.parseInt(split[1])).name().toLowerCase(Locale.ROOT);
                name = capitalizeFirstLetter(name);
                dayValue.setMonth(name.substring(0,3) + "‘24");
                dayValue.setTotalOrderRate((double)(totalOrderItemsMouth - aveOrderItems)/aveOrderItems * 100);
                dayValue.setSessionsRate((double)(sessionsTotal- aveSession)/aveSession * 100);
                months.add(dayValue);
            }

            DayValue value = new DayValue();
            value.setSortNum(0);
            value.setMonth("one");
            value.setSessionsRate(0);
            value.setTotalOrderRate(0);
            months.add(value);
            Collections.sort(modelList, Comparator.comparing(DaySaleModel::getDate));
            modelList.remove(modelList.size()-2);
           for(int i =0 ;i<modelList.size(); i++){
               if (i == modelList.size() -1 ){
                   DaySaleModel daySaleModel = modelList.get(i);
                   String mouth1 = daySaleModel.getMouth();
                   String[] split = mouth1.split("/");
                   int jj = Integer.parseInt(split[0]) - 1;
                   String ss = jj + "/" + split[1] + "/" + split[2];
                   daySaleModel.setMouth(ss);
               }
           }
            monthModel.setDaySaleModels(modelList);
            Collections.sort(months, Comparator.comparing(DayValue::getSortNum));
            monthModel.setMonths(months);
            List<DayValue> sessions = new ArrayList<>();
            List<String> strings = Constant.Map.get(months.get(1).getMonth().substring(0,3));
            List<String> strings1 = reversePrintList(strings);
            for (String str : strings1) {
                DayValue dayValue = new DayValue();
                Random random = new Random();
                int min = 80;
                int max = 130;
                int randomNumber = random.nextInt(max - min + 1) + min;
                dayValue.setMonth(str);
                dayValue.setSessionsRate(randomNumber);
                sessions.add(dayValue);
            }
            int ii = 0;
            for (DayValue month : months) {
                DayValue sessionValue = new DayValue();
                sessionValue.setMonth(month.getMonth());
                int a = (int) month.getSessionsRate();
                sessionValue.setSessionsRate(100 + a);
                if (ii == 1) {
                    sessions.add(sessionValue);
                }
                if (ii == 4) {
                    sessions.add(sessionValue);
                }
                if (ii == 7) {
                    sessions.add(sessionValue);
                }
                ii++;
            }
            monthModel.setSessions(sessions);

            DaySaleModel totalData = new DaySaleModel();

            double a = 0.0;
            double b = 0.0;
            int c = 0;
            int d = 0;
            int f = 0;
            int e = 0;
            double g = 0.0;
            double h = 0.0;
            double k = 0.0;
            double j = 0.0;
            double u = 0.0;
            double v = 0.0;
            int s = 0;
            int y = 0;
            double w = 0.0;
            double q = 0.0;
            int p = 0;
            for (DaySaleModel daySaleModel : modelList) {
                a += daySaleModel.getOrderedProductSales();
                b += daySaleModel.getOrderedProductSalesB2B();
                c += daySaleModel.getUnitsOrdered();
                d += daySaleModel.getUnitsOrderedB2B();
                f += daySaleModel.getTotalOrderItems();
                e += daySaleModel.getTotalOrderItemsB2B();
                g += daySaleModel.getSalesOrderItem();
                h += daySaleModel.getSalesOrderItemB2B();
                k += daySaleModel.getUnitsOrderItem();
                j += daySaleModel.getUnitsOrderItemB2B();
                u += daySaleModel.getAverageSellingPrice();
                v += daySaleModel.getAverageSellingPriceB2B();
                s += daySaleModel.getSessionsTotal();
                y += daySaleModel.getSessionsTotalB2B();
                w += daySaleModel.getOrderItemSessionPercentage();
                q += daySaleModel.getOrderItemSessionPercentageB2B();
                p += daySaleModel.getAverageOfferCount();
            }
            totalData.setOrderedProductSales(a);
            totalData.setOrderedProductSalesB2B(b);
            totalData.setUnitsOrdered(c);
            totalData.setUnitsOrderedB2B(d);
            totalData.setTotalOrderItems(f);
            totalData.setTotalOrderItemsB2B(e);
            totalData.setSalesOrderItem(g / modelList.size());
            totalData.setSalesOrderItemB2B(h / modelList.size());
            totalData.setUnitsOrderItem(k / modelList.size());
            totalData.setUnitsOrderItemB2B(j / modelList.size());
            totalData.setAverageSellingPrice(u / modelList.size());
            totalData.setAverageSellingPriceB2B(v / modelList.size());
            totalData.setSessionsTotal(s);
            totalData.setSessionsTotalB2B(y);
            totalData.setOrderItemSessionPercentage(w / modelList.size());
            totalData.setOrderItemSessionPercentageB2B(q / modelList.size());
            totalData.setAverageOfferCount(p / modelList.size());
            monthModel.setTotalData(totalData);


            Sheet sheet0 = workbook.getSheetAt(0);
            Row row = sheet0.getRow(1);
            String storeName = "";
            String webSite = "";
            if(row != null){
                Cell cell = row.getCell(0);
                storeName = cell.getStringCellValue();
                Cell cell1 = row.getCell(1);
                webSite = cell1.getStringCellValue();
                Cell moneyCell = row.getCell(18);
                totalData.setMoneyType(moneyCell.getStringCellValue());
            }
            monthModel.setStore(storeName);
            monthModel.setWebsite(webSite);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return monthModel;
    }

    @Override
    public TodayBusinessModel toDaybusinessReport() {
        TodayBusinessModel todayBusinessModel = new TodayBusinessModel();
        List<TodayData> todayDataList = new ArrayList<>();
        List<TodayData> yesterdayDataList = new ArrayList<>();
        List<TodayData> lastWeekDataList = new ArrayList<>();
        List<TodayData> lastYearDataList = new ArrayList<>();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(1);
            String storeName = "";
            String webSite = "";
            String moneyType = "";
            if(row != null){
                Cell cell = row.getCell(0);
                storeName = cell.getStringCellValue();
                Cell cell1 = row.getCell(1);
                webSite = cell1.getStringCellValue();
                Cell moneyCell = row.getCell(18);
                moneyType = moneyCell.getStringCellValue();
            }
            todayBusinessModel.setStore(storeName);
            todayBusinessModel.setWebsite(webSite);
            todayBusinessModel.setMoneyType(moneyType);

            // 处理当天营业数据
            Sheet sheet3 = workbook.getSheetAt(3);

            for (int i = 1; i <= sheet3.getLastRowNum(); i++) {
                TodayData todayData = new TodayData();
                TodayData yesterdayData = new TodayData();
                TodayData lastWeekData = new TodayData();
                TodayData lastYearData = new TodayData();
                Row row3 = sheet3.getRow(i);
                todayData.setTimeStr(row3.getCell(0).getStringCellValue());
                todayData.setOrderNum((int) row3.getCell(1).getNumericCellValue());
                todayData.setOrderMoney(row3.getCell(2).getNumericCellValue());
                todayDataList.add(todayData);
                yesterdayData.setTimeStr(row3.getCell(0).getStringCellValue());
                yesterdayData.setOrderNum((int) row3.getCell(3).getNumericCellValue());
                yesterdayData.setOrderMoney(row3.getCell(4).getNumericCellValue());
                yesterdayDataList.add(yesterdayData);
                lastWeekData.setTimeStr(row3.getCell(0).getStringCellValue());
                lastWeekData.setOrderNum((int) row3.getCell(5).getNumericCellValue());
                lastWeekData.setOrderMoney(row3.getCell(6).getNumericCellValue());
                lastWeekDataList.add(lastWeekData);
                lastYearData.setTimeStr(row3.getCell(0).getStringCellValue());
                lastYearData.setOrderNum((int) row3.getCell(7).getNumericCellValue());
                lastYearData.setOrderMoney(row3.getCell(8).getNumericCellValue());
                lastYearDataList.add(lastYearData);
            }
            todayBusinessModel.setTodayDataList(todayDataList);
            todayBusinessModel.setYesterdayDataList(yesterdayDataList);
            todayBusinessModel.setLastWeekDataList(lastWeekDataList);
            todayBusinessModel.setLastYearDataList(lastYearDataList);

            int totalOrderItems = 0;
            int unitsOrdered = 0;
            double orderedProductSales = 0L;
            for (TodayData todayData : todayDataList) {
                totalOrderItems += todayData.getOrderNum();
                unitsOrdered += todayData.getOrderNum();
                orderedProductSales += todayData.getOrderMoney();
            }
            if (unitsOrdered > 3 && unitsOrdered < 7) {
                unitsOrdered += 1;
            }
            if (unitsOrdered > 6 && unitsOrdered < 10) {
                unitsOrdered += 2;
            }
            if (unitsOrdered > 9) {
                unitsOrdered += 3;
            }
            int yesterdayOrderNum = 0;
            double yesterdaySales = 0L;
            for (TodayData todayData : yesterdayDataList) {
                yesterdayOrderNum += todayData.getOrderNum();
                yesterdaySales += todayData.getOrderMoney();
            }
            int lastWeekOrderNum = 0;
            double lastWeekSales = 0L;
            for (TodayData todayData : lastWeekDataList) {
                lastWeekOrderNum += todayData.getOrderNum();
                lastWeekSales += todayData.getOrderMoney();
            }
            int lastYearOrderNum = 0;
            double lastYearSales = 0L;
            for (TodayData todayData : lastYearDataList) {
                lastYearOrderNum += todayData.getOrderNum();
                lastYearSales += todayData.getOrderMoney();
            }
            // 总览5个字段
            todayBusinessModel.setTotalOrderItems(totalOrderItems);
            todayBusinessModel.setUnitsOrdered(unitsOrdered);
            todayBusinessModel.setOrderedProductSales(orderedProductSales);
            double unitsOrderItem = (double) unitsOrdered / totalOrderItems;
            String unitsOrderedFormatted = "";
            if (unitsOrdered > 3) {
                DecimalFormat df = new DecimalFormat("#.00");
                unitsOrderedFormatted = df.format(unitsOrderItem);
            } else {
                unitsOrderedFormatted = "1";
            }
            todayBusinessModel.setUnitsOrderItem(unitsOrderedFormatted);
            double averageSellingPrice = orderedProductSales / unitsOrdered;
            DecimalFormat df = new DecimalFormat("#.00");
            String formattedValue = df.format(averageSellingPrice);
            todayBusinessModel.setAverageSellingPrice(formattedValue);

            todayBusinessModel.setTodayOrderNum(totalOrderItems);
            todayBusinessModel.setTodaySales(df.format(orderedProductSales));
            todayBusinessModel.setYesterdayOrderNum(yesterdayOrderNum);
            todayBusinessModel.setYesterdaySales(df.format(yesterdaySales));
            todayBusinessModel.setLastWeekOrderNum(lastWeekOrderNum);
            todayBusinessModel.setLastWeekSales(df.format(lastWeekSales));
            todayBusinessModel.setLastYearOrderNum(lastYearOrderNum);
            todayBusinessModel.setLastYearSales(df.format(lastYearSales));

            Sheet sheet4 = workbook.getSheetAt(4);
            Row row4 = sheet4.getRow(0);
            todayBusinessModel.setBusinessPerformanceInsights(row4.getCell(0).getStringCellValue());
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return todayBusinessModel;
    }

    @Override
    public AdvertModel getAdverInfo() {
        AdvertModel advertModel = new AdvertModel();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(6);
            Row row = sheet.getRow(1);
            advertModel.setDate(row.getCell(0).getStringCellValue());
            advertModel.setChoice((int) row.getCell(1).getNumericCellValue());
            advertModel.setImageUrl(row.getCell(2).getStringCellValue());
            advertModel.setProductTitle(row.getCell(3).getStringCellValue());
            advertModel.setProductCode(row.getCell(4).getStringCellValue());
            advertModel.setSummary(row.getCell(5).getStringCellValue());
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return advertModel;
    }

    public static String capitalizeFirstLetter(String str) {
        if (str == null || str.isEmpty()) {
            return str;
        }
        return str.substring(0, 1).toUpperCase() + str.substring(1);
    }

    public static List<String> reversePrintList(List<String> list) {
        List<String> ll = new ArrayList<>();
        for (int i = list.size() - 1; i >= 0; i--) {
            ll.add(list.get(i));
        }
        return ll;
    }
}
